package com.yspa.dbutil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.yspa.util.DateUtils;

public class DBConnOperator {
	protected Logger log = Logger.getLogger(DBConnOperator.class);

	private SQLServerConnection sqlconn;

	public DBConnOperator() {
	}
	public  Connection getConnection()
	{
		Connection conn = null;
		try
		{
			if (null == sqlconn){
				sqlconn = new SQLServerConnection();
			}
			return sqlconn.getConnection();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return conn;
	}
////////////////////////////////////////////////////////////////////////////
	public boolean executeUpdate(String sql) {
		PreparedStatement ps = null;
		Connection conn = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate(sql);
			conn.commit();
			return true;
		} catch (Exception e) {
			log.error("异常:" + e);
			log.error("异常sql：" + sql);
			e.printStackTrace();
			return false;
		} finally {
			clean(ps);
			close(conn);
		}
	}


	public ResultSet executeQuery(String sql)
			throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			rs = conn.createStatement().executeQuery(sql);
			return rs;
		} catch (Exception e) {
			log.error("异常:" + e);
			log.error("异常sql：" + sql);
			e.printStackTrace();
			this.clean(rs,null,null);
			return null;
		} finally {
			close(conn);
		}

	}



	protected void clean(ResultSet rs, PreparedStatement ps, Statement st) {
		if (rs != null){
			try {
				Statement st2 = rs.getStatement();
				if(st2 != null){
					st2.close();
				}
				rs.close();
			} catch (Exception e) {
				log.error("异常:" + e);
				e.printStackTrace();
			}
		}
		if (ps != null){
			try {
				ps.close();
			} catch (Exception e) {
				log.error("异常:" + e);
				e.printStackTrace();
			}
		}
		if(st != null){
			try {
				st.close();
			} catch (Exception e) {
				log.error("异常:" + e);
				e.printStackTrace();
			}
		}
	}

	protected void clean(ResultSet rs, PreparedStatement ps) {
		this.clean(rs, ps, null);
	}

	protected void clean(PreparedStatement ps) {
		this.clean(null, ps, null);
	}

	protected void clean(Statement st) {
		this.clean(null, null, st);
	}

	protected void clean(ResultSet rs) {
		this.clean(rs, null, null);
	}




	protected void rollback(Connection conn){
		try {
			conn.rollback();
		} catch (Exception e) {
			log.error("异常:" + e);
			e.printStackTrace();
		}
	}
	
	/**
	 * @方法描述：执行批处理的sql[]语句
	 * @输入：sql[]语句
	 * @输出：
	 */
	public boolean executeBatch(String[] sql) {

		Connection conn = null;
		Statement st = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			for (int i = 0; i < sql.length; i++) {
				st.addBatch(sql[i]);
			}
			st.executeBatch();
			conn.commit();
			return true;
		} catch (Exception e) {
			rollback(conn);
			log.error("异常:" + e);
			for (int i = 0; i < sql.length; i++) {
				log.error("异常sql@ 第"+ (i + 1) + "行：" + sql[i]);
			}
			e.printStackTrace();
			return false;
		} finally {
			clean(st);
			close(conn);
		}
	}


	public boolean executeBatch(ArrayList al) {
		Connection conn = null;
		Statement st = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			for (int i = 0; i < al.size(); i++) {
				st.addBatch((String) al.get(i));
			}
			st.executeBatch();
			conn.commit();
			return true;
		} catch (Exception e) {
			rollback(conn);
			log.error("异常:" + e);
			for (int i = 0; i < al.size(); i++) {
				log.error("异常sql@  第" + (i + 1) + "行：" + al.get(i));
			}
			e.printStackTrace();
			return false;
		} finally {
			clean(st);
			close(conn);
		}
	}



	/**
	 * @方法描述：执行存储过程
	 * 
	 */
	public void executeMemory(String Memory) {
		if (Memory != null) 
		{
			Connection conn = null;
			conn = getConnection();
			PreparedStatement pstmt = null;
			try 
			{
				pstmt = conn.prepareCall(Memory);
				pstmt.execute();
			} catch (Exception e)
			{
				log.error("异常:" + e);
				log.error("异常sql：" + Memory);
				e.printStackTrace();
			} finally {
				clean(pstmt);
				close(conn);
			}
		}
	}


	protected void closeStatement(ResultSet rs) {
		if (rs != null) {
			try {
				if (rs.getStatement() != null) {
					rs.getStatement().close();
				}
			} catch (Exception e) {
				log.error("异常:" + e);
				e.printStackTrace();
			}
		}
	}



	public void commit(Connection conn, Statement st) {
		try {
			conn.commit();
		} catch (Exception e) {
			log.error("手动提交异常："+e);
			e.printStackTrace();
		}
	}

	public void rollbake(Connection conn, Statement st) {
		this.rollback(conn);
		this.clean(st);
	}
	
	/**
	 * 返回LIST的标准getlist方法
	 * @param sql
	 * @param poolName
	 * @return
	 */
	public List<Map<String,Object>> getList(String sql) {
		log.debug("当前方法：getList");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		List<Map<String,Object>> pkv = new ArrayList<Map<String,Object>>();
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			log.debug("sql:"+sql);
			rsmd = rs.getMetaData();
			int num = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				for (int i = 1; i <= num; i++) {
					String key = rsmd.getColumnName(i);
					int colType = rsmd.getColumnType(i);
					if (colType == Types.VARCHAR || colType == Types.CHAR) {
						String value = rs.getString(i);
						map.put(key, value);
					} else if (colType == Types.NUMERIC) {
						BigDecimal value = rs.getBigDecimal(i);
						map.put(key, value);
					} else if (colType == Types.TIMESTAMP || colType == Types.DATE) {
						Date value = null;
						value = rs.getTimestamp(i);
						map.put(key, value);
						map.put(key+"_SHOW_DATE", DateUtils.getTimeStr(value)[1]);
						map.put(key+"_FORMAT_DATE", DateUtils.getTimeStr(value)[0]);
					}
				}
				pkv.add(map);
			}
			return pkv;
		} catch (Exception e) {
			log.error("异常12："+e);
			log.error("异常sql@ ："+sql);
			e.fillInStackTrace();
			return null;
		} finally {
			this.clean(rs, null, stmt);
			close(conn);
		}
	}
	
	protected int getCount(String sql) 
	{
		int count = 0;
		ResultSet rs=null;
		String sql2 = "select count(1) count1 from (" + sql + ") ";
		try 
		{
		    rs= this.executeQuery(sql2);		
			count = rs.getInt("count1");
			return count;
			
		} catch (Exception e) 
		{
			e.printStackTrace();
			log.error("异常13："+e);
			log.error("异常sql:"+sql);
			return -1;
		} finally 
		{
            this.clean(rs,null,null);
		}
	}
	
	private void close(Connection conn){
		try
		{
			if (conn != null)
			{
	
				conn.close();
			}
		}
		catch (SQLException ex)
		{
	      //	 Ignore any exceptions during close
		}
		conn  = null;
	}
	public static void main(String[] args){
		DBConnOperator db =  new DBConnOperator();
		String sql = "SELECT * FROM Abnormal ";
		List<Map<String, Object>> list = db.getList(sql);
		for (int i = 0; i < list.size(); i++){
			Map<String, Object> map = list.get(i);
			Iterator<?> mlist = map.entrySet().iterator();
			while (mlist.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) mlist.next();
				System.out.println(entry.getKey()+": " + map.get(entry.getKey()));
			}
			System.out.println("-----------------------------------------------------------------------");
		}
	}
}